--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_art_collection AS (SELECT * FROM &erp.art_collection WHERE 1=2);




 delete from se_art_collection;


 INSERT INTO se_art_collection
 (SELECT id collection_cod,
         0 enreg_id,
         regroup collectionregr_cod,
         'N' enreg_s,
         libelle collection_lib,
         NULL collection_lib2,
         REGRSTAT critlibre1_val,
         SPECIF1 critlibre2_val
    FROM UV_CCOLLECTIONS) ;

 INSERT INTO se_art_collection
 (SELECT COMBATTVAL_VAL collection_cod,
         0 enreg_id,
         null collectionregr_cod,
         'N' enreg_s,
         'Pour reprise article vrt=TPR1' collection_lib,
         NULL collection_lib2,
         null critlibre1_val,
         null critlibre2_val
  FROM temp_se_VRT_COMBSTOVAL t WHERE vrt_cod ='TPR' AND att_cod =1
  AND NOT EXISTS (SELECT 1 FROM se_art_collection c WHERE c.collection_cod = t.combattval_val)
  AND t.combattval_val IS NOT null
  AND Length(COMBATTVAL_VAL)<=3
  GROUP BY COMBATTVAL_VAL
  );



 UPDATE se_art_collection tf SET enreg_id=(select enreg_id FROM &erp.art_collection f WHERE f.collection_cod=tf.collection_cod) ;
 UPDATE se_art_collection tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0;



 CALL uvse_synchroverserp('collection_cod','ART_COLLECTION','O') ;

 commit;
 
 
 select * from (
SELECT &outils.stragg(source) source,collection_cod, enreg_id, collectionregr_cod, enreg_s, collection_lib, collection_lib2, critlibre1_val, critlibre2_val
FROM (
  SELECT 'uv' source ,g.* FROM se_art_collection g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.art_collection g
  )
GROUP BY collection_cod, enreg_id, collectionregr_cod, enreg_s, collection_lib, collection_lib2, critlibre1_val, critlibre2_val
HAVING Count(1)!=2) r
ORDER BY collection_cod;